package com.greateamd.timetableasy.server.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.greateamd.timetableasy.domain.Event;
import com.greateamd.timetableasy.domain.University;
import com.greateamd.timetableasy.domain.exceptions.DBServerSideException;
import com.greateamd.timetableasy.domain.exceptions.IllegalEntityStateException;
import com.greateamd.timetableasy.server.ServerUtil;
import com.greateamd.timetableasy.server.dao.UniversityDAO;
import com.greateamd.timetableasy.server.dao.util.AbstractHibernateDAO;

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, readOnly = false)	
@Repository("universityDAO")
public class UniversityDAOImpl extends AbstractHibernateDAO<University,Integer> implements UniversityDAO{
	
	public UniversityDAOImpl()
	{
		super(new University());
	}

	@Override
	public List<Event> getPlanning(final University university, final Date from, final Date to) throws DBServerSideException, IllegalEntityStateException {
		logger.debug("Get "+university.getId()+"'s university planning from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				final List<Event> events = new ArrayList<Event>();
				
				//get sp events
				String hql = "SELECT distinct(event) " +
				 "FROM University univ " +
				 "JOIN univ.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE univ.id = :univId " +
				 "ORDER BY event.startDate";
				Query query = session.createQuery(hql);
				query.setParameter("univId", university.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				List list = query.list();
				events.addAll(list);
				
				ServerUtil.sortStartDateList(events);
				
				return events;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public University findByName(final String name) throws DBServerSideException {
		HibernateCallback<University> callback = new HibernateCallback<University>() {
			@Override
			public University doInHibernate(Session session) throws HibernateException,SQLException {
				return (University) session.createCriteria(University.class).add(Restrictions.eq("name", name)).uniqueResult();
			}
		};
	    return getHibernateTemplate().execute(callback);
	}

}
